﻿Imports WRDB.DBProviders

Public MustInherit Class clsImport
    Public Enum enumConnectionType
        File
        SCADA
        Aquarius
        Hydrolynx
    End Enum

    Friend Const DeleteImportFileWhenDone As Boolean = True 'set to true for production

    Public Property Activated As Boolean = True
    Public Property ConnectionName As String = ""
    Public Property ConnectionType As enumConnectionType
    Public Property WRDBProjectName As String = ""
    Public Property WRDBTable As String = ""
    Public Property MinDays As Integer = 5
    Public Property MaxDays As Integer = 60
    Public Property OverrideDateRange As Boolean = False
    Public Property FirstDate As Date = DateTimePicker.MinimumDateTime
    Public Property LastDate As Date = DateTimePicker.MaximumDateTime
    Friend Project As clsProject = Nothing
    Public Property MappingFile As String = ""
    Protected Friend dictMapping As Dictionary(Of String, clsMapping) = Nothing
    Protected Friend FirstImportDate As Date, LastImportDate As Date, DeleteBeforeDate As Date
    Public Property ErrorsOccurred As Boolean = False

    Public Shared Function GetImport(SectionName As String) As clsImport
        Dim ConType As enumConnectionType = enumConnectionType.File
        If Not [Enum].TryParse(GetKey(SectionName, enumTaskKey.ConnectionType, enumConnectionType.File.ToString), ConType) Then Return Nothing
        Dim Import As clsImport = Nothing
        Select Case ConType
            Case enumConnectionType.File : Import = New clsImportFile(GetKey(SectionName, enumTaskKey.ConnectionName, ""))
            Case enumConnectionType.SCADA : Import = New clsImportSCADA(GetKey(SectionName, enumTaskKey.ConnectionName, ""))
            Case enumConnectionType.Aquarius : Import = New clsImportAquarius(GetKey(SectionName, enumTaskKey.ConnectionName, ""))
            Case enumConnectionType.Hydrolynx : Import = New clsImportHydrolynx(GetKey(SectionName, enumTaskKey.ConnectionName, ""))
        End Select
        Return Import
    End Function

    Public Overridable Sub Load(SectionName As String)
        WRDBProjectName = GetKey(SectionName, enumTaskKey.WRDBProjectName, "")
        WRDBTable = GetKey(SectionName, enumTaskKey.WRDBTable, "")
        MinDays = GetKey(SectionName, enumTaskKey.MinDays, "")
        MaxDays = GetKey(SectionName, enumTaskKey.MaxDays, "")
        MappingFile = GetKey(SectionName, enumTaskKey.MappingFile, "")
        Activated = GetKey(SectionName, enumTaskKey.Activated, True)
        OverrideDateRange = GetKey(SectionName, enumTaskKey.OverrideDateRange, False)
        FirstDate = Date.Parse(GetKey(SectionName, enumTaskKey.FirstDate, DateTimePicker.MinimumDateTime), Globalization.CultureInfo.InvariantCulture)
        LastDate = Date.Parse(GetKey(SectionName, enumTaskKey.LastDate, DateTimePicker.MaximumDateTime), Globalization.CultureInfo.InvariantCulture)
    End Sub

    Public Overridable Sub Save(SectionName As String)
        SetKey(SectionName, enumTaskKey.ConnectionName, ConnectionName)
        SetKey(SectionName, enumTaskKey.ConnectionType, ConnectionType.ToString)
        SetKey(SectionName, enumTaskKey.WRDBProjectName, WRDBProjectName)
        SetKey(SectionName, enumTaskKey.WRDBTable, WRDBTable)
        SetKey(SectionName, enumTaskKey.MinDays, MinDays)
        SetKey(SectionName, enumTaskKey.MaxDays, MaxDays)
        SetKey(SectionName, enumTaskKey.MappingFile, MappingFile)
        SetKey(SectionName, enumTaskKey.Activated, Activated)
        SetKey(SectionName, enumTaskKey.OverrideDateRange, OverrideDateRange)
        SetKey(SectionName, enumTaskKey.FirstDate, FirstDate.ToString(Globalization.CultureInfo.InvariantCulture))
        SetKey(SectionName, enumTaskKey.LastDate, LastDate.ToString(Globalization.CultureInfo.InvariantCulture))
    End Sub

    Protected Function LoadMapping() As Boolean
        'to import the data open mapping file which must contain the following columns (tab-separated):
        'Col1: TAGNAME
        'Col2: Station_ID (optional)
        'Col3: PCode (optional)
        'Col4: Depth (optional)
        'Col5: LCode (optional)

        'only tags identified in this mapping file will be imported; if a tag is found in mapping
        'file but not in table, it will be ignored but a warning will be issued

        'note: the file should be in CultureInvariant format (e.g., 1.234 not 1,234); this really only matters for the depth column
        '(if comma is found in depth field, is converted to decimal point)

        Try
            dictMapping = New Dictionary(Of String, clsMapping) 'column heading-->stationid, pcode, depth, lcode

            If String.IsNullOrEmpty(MappingFile) Or Not My.Computer.FileSystem.FileExists(MappingFile) Then Return True 'don't return error

            If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "Reading mapping file..." : Application.DoEvents()

            If My.Computer.FileSystem.FileExists(MappingFile) Then
                Dim tfp As New FileIO.TextFieldParser(MappingFile)
                tfp.SetDelimiters(vbTab)
                tfp.ReadLine() 'skip header line
                While Not tfp.EndOfData
                    Dim ar() As String = tfp.ReadFields
                    If ar.Length >= 1 AndAlso Not dictMapping.ContainsKey(ar(0)) Then
                        Dim Station_ID As String = "", PCode As String = "", Depth As Double = Double.NaN, LCode As String = ""
                        If ar.Length >= 2 Then Station_ID = Strings.Left(ar(1), 25).ToUpper
                        If ar.Length >= 3 Then PCode = Strings.Left(ar(2), 10).ToUpper
                        If ar.Length >= 4 AndAlso ar(3).Trim <> "" Then
                            Depth = Double.Parse(ar(3).Replace(",", "."), Globalization.CultureInfo.InvariantCulture)
                        End If
                        If ar.Length >= 5 AndAlso ar(4).Trim <> "" Then
                            LCode = Strings.Left(ar(4), 3).ToUpper
                        End If
                        dictMapping.Add(ar(0), New clsMapping With {.StationID = Station_ID, .PCode = PCode, .Depth = Depth, .LCode = LCode})
                    End If
                End While
                tfp.Close()
                tfp.Dispose()
                Return True
            Else
                AppendLog("Unable to find specified import mapping file: {0}.", MappingFile)
                SetTrayText("An error occurred; check your settings and log file.")
                Return False
            End If
        Catch ex As Exception
            ErrorsOccurred = True
            Return False
        Finally
            If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "" : Application.DoEvents()
        End Try
    End Function

    Protected Function SaveMapping() As Boolean
        Try

            Dim frmSave As New SaveFileDialog
            With frmSave
                .AddExtension = True
                .CheckPathExists = True
                .DefaultExt = ".txt"
                .FileName = MappingFile
                .Filter = "Mapping file|*.txt"
                .OverwritePrompt = True
                .Title = "Build Mapping File"
                .ValidateNames = True
            End With
            If frmSave.ShowDialog = DialogResult.OK Then
                MappingFile = frmSave.FileName
                If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "Writing mapping file..." : Application.DoEvents()
                Dim sw As New IO.StreamWriter(MappingFile)
                sw.WriteLine($"Tag ID{vbTab}Station ID{vbTab}PCode{vbTab}Depth")
                For Each kv In dictMapping
                    sw.WriteLine($"{kv.Key}{vbTab}{kv.Value.StationID}{vbTab}{kv.Value.PCode}{vbTab}{TestNull(kv.Value.Depth, "")}{vbTab}{kv.Value.LCode}")
                Next
                sw.Close()
                sw.Dispose()
            End If
            frmSave.Dispose()
            Return True

        Catch ex As Exception

            ErrorMsg(, ex)
            Return False

        Finally

            If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "" : Application.DoEvents()

        End Try
    End Function

    Public Overridable Sub BuildMapping(MappingFile As String)
        Me.MappingFile = MappingFile
    End Sub

    Public Overridable Sub Import()
        ErrorsOccurred = False
        Project = New clsProject(WRDBProjectName)

        If Project Is Nothing OrElse Project.DB Is Nothing Then
            Dim s As String = $"Unable to create project called {WRDBProjectName} or connect to the database specified therein when attempting to perform import; please check your configuration and status of associated database."
            AppendLog(s)
            Throw New Exception(s)
        End If
        AddHandler Project.Progress, AddressOf Progress

        'retrieve the data since the last previously downloaded value; in addition, add MinDays of overlapping data
        Dim lastdt As Date = TestNull(Project.DB.ExecuteScalar("Select MAX(Date_Time) FROM {0}", Project.DB.AddBrackets(WRDBTable)), Date.MaxValue)
        If lastdt <> Date.MaxValue Then lastdt = lastdt.AddDays(-MinDays)

        'optionally delete table records older than MaxDays old
        Dim firstdt As Date = Now.AddYears(-100)
        If MaxDays <> 0 Then firstdt = Now.AddDays(-MaxDays)

        'if WRDB table is empty, retrieve maximum number of days of data
        If Project.GetRecordCount(WRDBTable) = 0 Then lastdt = firstdt

        lastdt = lastdt.Date

        If OverrideDateRange Then
            FirstImportDate = FirstDate
            LastImportDate = LastDate.AddDays(1).AddSeconds(-1) 'want it to be inclusive of this date
            DeleteBeforeDate = Date.MinValue
        Else
            FirstImportDate = lastdt
            LastImportDate = Now
            DeleteBeforeDate = firstdt.Date
        End If
    End Sub

End Class

Public Class clsImportFile : Inherits clsImport
    Public Property ImportFolder As String = ""

    Public Sub New(ConnectionName As String)
        MyBase.New()
        Me.ConnectionName = ConnectionName
        Me.ConnectionType = enumConnectionType.File
    End Sub

    Public Overrides Sub Load(SectionName As String)
        MyBase.Load(SectionName)
        ImportFolder = GetKey(SectionName, enumTaskKey.ImportFolder, "")
    End Sub

    Public Overrides Sub Save(SectionName As String)
        MyBase.Save(SectionName)
        SetKey(SectionName, enumTaskKey.ImportFolder, ImportFolder)
    End Sub

    Public Overrides Sub BuildMapping(MappingFile As String)
        MessageBox.Show("Use the WRDB File Import tool To create the file import mapping file. Open a Working table, Select Edit | Import | Import Data File And select a typical file that you want to import. Next the Preview Import Data form Is displayed. Interactively set the column mappings as desired And click the Save button to create a HDR file that can be selected here.")
    End Sub

    Public Overrides Sub Import()
        Try

            MyBase.Import()

            If Not My.Computer.FileSystem.DirectoryExists(ImportFolder) Then
                AppendLog("Unable to find specified import folder {0}.", ImportFolder)
                SetTrayText("An error occurred; check your settings and log file.")
                Exit Sub
            End If

            Try
                Dim PrintDoc As New Drawing.Printing.PrintDocument
                Dim WRDBImport As New Import.clsWRDBImport(PrintDoc, Project)
                With WRDBImport
                    For Each f As String In My.Computer.FileSystem.GetFiles(ImportFolder)
                        If ImportProgressForm.IsCancelled Then
                            AppendLog($"Import cancelled by user while importing {f} to {WRDBTable}.")
                            ErrorsOccurred = True
                            Exit For
                        End If
                        Try
                            SetTrayText("Connection: {0}; Importing file: {1}", ConnectionName, IO.Path.GetFileName(f))

                            .ReadHeaderAndImport(Me.MappingFile, f)
                            Dim NumRecords As Integer = Project.GetRecordCount(Me.WRDBTable)

                            If ImportProgressForm.IsCancelled Then Exit For
                            If Project.ImportTable(Me.WRDBTable, .DataTable, True, True) Then
                                If IsCancelled Then Exit For
                                Project.UpdateTrackingRecord(WRDBTable, Project.GetRecordCount(WRDBTable), True)
                                If DeleteBeforeDate <> Date.MinValue Then
                                    Project.DB.ExecuteNonQuery($"DELETE FROM {Project.FullTableName(WRDBTable)} 
                                                           WHERE Date_Time<{Project.DB.DateTime(DeleteBeforeDate)}")
                                End If
                            End If
                            .Dispose()

                            NumRecords = Project.GetRecordCount(WRDBTable) - NumRecords
                            AppendLog("Imported {0} records to {1}.", NumRecords, WRDBTable)
                            If DeleteImportFileWhenDone Then My.Computer.FileSystem.DeleteFile(f)

                        Catch ex As Exception
                            AppendLog("Unable to import file: {0}--{1}.", f, ex.Message)
                            ErrorsOccurred = True
                        End Try
                    Next
                End With

            Catch ex As Exception
                AppendLog("Unable to open project: {0}.", WRDBProjectName)
                ErrorsOccurred = True

            Finally
                If Project IsNot Nothing Then RemoveHandler Project.Progress, AddressOf Progress
            End Try

        Catch ex As Exception

            ErrorMsg(, ex)

        End Try

    End Sub

End Class

Public Class clsImportHydrolynx : Inherits clsImport
    Public Property ImportFolder As String = ""
    Public Property Resample As Boolean = False
    Public Property ResampleInterval As Integer = 60
    Private Const MinAllowedResult As Double = 0.0
    Private Const MaxAllowedResult As Double = 10000000000.0

    Public Sub New(ConnectionName As String)
        MyBase.New()
        Me.ConnectionName = ConnectionName
        Me.ConnectionType = enumConnectionType.Hydrolynx
    End Sub

    Public Overrides Sub Load(SectionName As String)
        MyBase.Load(SectionName)
        ImportFolder = GetKey(SectionName, enumTaskKey.ImportFolder, "")
        Resample = GetKey(SectionName, enumTaskKey.Resample, True)
        ResampleInterval = GetKey(SectionName, enumTaskKey.ResampleInterval, 60)
    End Sub

    Public Overrides Sub Save(SectionName As String)
        MyBase.Save(SectionName)
        SetKey(SectionName, enumTaskKey.ImportFolder, ImportFolder)
        SetKey(SectionName, enumTaskKey.Resample, Resample)
        SetKey(SectionName, enumTaskKey.ResampleInterval, ResampleInterval)
    End Sub

    Public Overrides Sub BuildMapping(MappingFile As String)

        MyBase.BuildMapping(MappingFile)

        'assemble list of unique column headings
        Dim lstColNames As New List(Of String)
        For Each fn In My.Computer.FileSystem.GetFiles(ImportFolder, FileIO.SearchOption.SearchTopLevelOnly, "*.csv")
            Dim tfpImport As New FileIO.TextFieldParser(fn)
            tfpImport.SetDelimiters(",")
            Dim s() = tfpImport.ReadFields
            For i As Integer = 1 To s.Length - 1
                If Not lstColNames.Contains(s(i)) Then lstColNames.Add(s(i))
            Next
            tfpImport.Close()
            tfpImport.Dispose()
        Next

        If Not LoadMapping() Then Exit Sub

        If lstColNames.Count = 0 Then
            WarningMsg("No import files were found in the specified import folder. One or more properly formatted example files must exist there so that the mapping file can be built. The last version of the Mapping file will be used instead.")
            lstColNames.AddRange(dictMapping.Keys)
            If lstColNames.Count = 0 Then
                WarningMsg("Unable to find import columns in mapping file.")
                Exit Sub
            End If
        End If

        lstColNames.Sort()

        With New frmBuildMapping
            .dictMapping = dictMapping 'so can display mapped station id, pcode & depth when selected
            .Project = New clsProject(WRDBProjectName)
            With .TreeView1
                .CheckBoxes = True
                For Each tag In lstColNames
                    With .Nodes.Add(tag)
                        .Checked = dictMapping.ContainsKey(tag)
                    End With
                Next
                .SelectedNode = Nothing
                .CollapseAll()
            End With

            If .ShowDialog = DialogResult.OK Then SaveMapping()

        End With
    End Sub

    Public Overrides Sub Import()

        Dim tfpData As FileIO.TextFieldParser = Nothing
        Dim tran As Common.DbTransaction = Nothing

        MyBase.Import()

        If Not My.Computer.FileSystem.DirectoryExists(ImportFolder) Then
            AppendLog("Unable to find specified import folder: {0}.", ImportFolder)
            SetTrayText("An error occurred; check your settings and log file.")
            Exit Sub
        End If

        Try

            If Not LoadMapping() Then Exit Sub

            'table format is as follows:
            'Col 1: DATE    TIME (datetime field) CULTURE INVARIANT
            'Col 2,3, etc.: 9784/inst (value exported for instrument)

            Dim recid As Integer = TestNull(Project.DB.ExecuteScalar("SELECT MAX(RecID) FROM {0}", Project.DB.AddBrackets(WRDBTable)), 0)
            Dim sql As String = ""
            Dim filenum As Integer = -1
            Dim fnList = My.Computer.FileSystem.GetFiles(ImportFolder, FileIO.SearchOption.SearchTopLevelOnly, "*.csv")
            Dim NumRecords As Integer = 0
            tran = Project.DB.BeginTransaction

            'for each file in import folder, attempt to import it; obey mindays and date range settings
            For Each fn In fnList

                Try

                    'for each file determine the range of dates contained therein and delete only those records
                    'this will prevent duplicates from being imported if the Hydrolynx export files aren't perfectly formed

                    tfpData = New FileIO.TextFieldParser(fn)
                    tfpData.SetDelimiters(",")

                    Dim lstColumns As New List(Of String)
                    lstColumns.AddRange(tfpData.ReadFields)

                    filenum += 1
                    If tfpData.EndOfData Then tfpData.Close() : Continue For

                    Dim dt1 As Date = Date.Parse(tfpData.ReadFields(0), Globalization.CultureInfo.InvariantCulture)
                    Dim dt2 As Date = dt1
                    While Not tfpData.EndOfData
                        dt2 = Date.Parse(tfpData.ReadFields(0), Globalization.CultureInfo.InvariantCulture)
                    End While
                    tfpData.Close()

                    'don't delete records outside of desired date range

                    If dt1 < FirstImportDate Then dt1 = FirstImportDate
                    If dt2 > LastImportDate Then dt2 = LastImportDate

                    'form list for all import columns (just to be safe; each file actually only imports a single tag)
                    Dim itemlist As String = ""
                    For i As Integer = 1 To lstColumns.Count - 1
                        If dictMapping.ContainsKey(lstColumns(i)) Then
                            itemlist &= IIf(i = 1, "", ",") & $"'{dictMapping(lstColumns(i)).StationID}~{dictMapping(lstColumns(i)).PCode}~{dictMapping(lstColumns(i)).LCode}'"
                        End If
                    Next
                    sql = $"DELETE FROM {Project.FullTableName(WRDBTable)} 
                            WHERE Station_ID + '~' + PCode + '~' + LCode IN ({itemlist}) 
                            AND Date_Time BETWEEN {Project.DB.DateTime(dt1)} 
                                              AND {Project.DB.DateTime(dt2)}"
                    Project.DB.ExecuteNonQuery(tran, sql)

                    tfpData = New FileIO.TextFieldParser(fn)
                    tfpData.SetDelimiters(",")

                    Dim TotalLines As Integer = My.Computer.FileSystem.ReadAllText(fn).Split(vbLf).Length

                    tfpData.ReadLine()

                    While Not tfpData.EndOfData

                        Dim s() As String = tfpData.ReadFields
                        Dim DateTime As Date = Date.Parse(s(0), Globalization.CultureInfo.InvariantCulture)
                        If DateTime >= FirstImportDate AndAlso DateTime <= LastImportDate AndAlso (Not Resample OrElse DateTime.Minute Mod ResampleInterval = 0) Then
                            For Each kv In dictMapping
                                If lstColumns.Contains(kv.Key) Then
                                    Dim StationID As String = kv.Value.StationID
                                    Dim PCode As String = kv.Value.PCode
                                    Dim Depth As Double = kv.Value.Depth
                                    Dim LCode As String = kv.Value.LCode
                                    Progress(Nothing, filenum, fnList.Count, $"Importing Hydrolynx file to {StationID }/{PCode}/{LCode}...")
                                    If s(lstColumns.IndexOf(kv.Key)) <> "" Then
                                        Dim result As Double = Double.Parse(s(lstColumns.IndexOf(kv.Key)), Globalization.CultureInfo.InvariantCulture)
                                        If result >= MinAllowedResult And result <= MaxAllowedResult Then
                                            recid += 1
                                            sql = $"INSERT INTO {Project.FullTableName(WRDBTable)} 
                                            (RecID, Station_ID, PCode, Date_Time, Result, Depth_M, LCode, Create_Update, Owner) 
                                            VALUES (
                                                {recid}, 
                                                {Project.DB.TextString(StationID.ToUpper, 25)}, 
                                                {Project.DB.TextString(PCode.ToUpper, 10)}, 
                                                {Project.DB.DateTime(DateTime)}, 
                                                {Project.DB.Numeric(result)}, 
                                                {Project.DB.Numeric(Depth)}, 
                                                {Project.DB.TextString(LCode.ToUpper, 3)}, 
                                                {Project.DB.DateTime(Now)}, 
                                                {Project.DB.TextString(clsApplicationSettings.UserName, 3)})"

                                            Project.DB.ExecuteNonQuery(tran, sql)
                                            NumRecords += 1
                                            Application.DoEvents()
                                            If ImportProgressForm.IsCancelled Then
                                                AppendLog($"Import cancelled by user while importing {fn} to {WRDBTable}.")
                                                ErrorsOccurred = True
                                                tran.Rollback()
                                                Exit Sub
                                            End If
                                        End If
                                    End If
                                End If
                            Next
                        End If

                    End While

                    tfpData.Close()
                    tfpData.Dispose()

                Catch ex As Exception

                    AppendLog($"Error occurred while importing {fn} to {WRDBTable}: {ex.Message}.")
                    ErrorsOccurred = True

                End Try

                'note: import files are NOT deleted after import because they reside on read-only linux server
                'that server is configured to delete files older than 30 days so not need to worry about cleaning up
                'is a bit wasteful to try to import 30 days of data each night (most of which will be out of import
                'time range) but this will be most reliable way to do it and won't take that long
            Next

            If DeleteBeforeDate <> Date.MinValue Then
                Project.DB.ExecuteNonQuery(tran, $"DELETE FROM {Project.FullTableName(WRDBTable)} 
                                                           WHERE Date_Time<{Project.DB.DateTime(DeleteBeforeDate)}")
            End If

            'update the support tables using mapping file

            Dim lstStationIDs As New List(Of String)
            Dim lstPCodes As New List(Of String)

            For Each kv In dictMapping
                If Not lstStationIDs.Contains(kv.Value.StationID) Then lstStationIDs.Add(kv.Value.StationID)
                If Not lstPCodes.Contains(kv.Value.PCode) Then lstPCodes.Add(kv.Value.PCode)
            Next

            For Each StationID In lstStationIDs
                sql = $"IF NOT EXISTS (SELECT * FROM {Project.FullTableName(clsProject.enumTableTypes.Stations)} WHERE Station_ID={Project.DB.TextString(StationID, 25)}) 
                            INSERT INTO {Project.FullTableName(clsProject.enumTableTypes.Stations)} (Station_ID, Station_Name, Create_Update) 
                            VALUES (
                                {Project.DB.TextString(StationID, 25)},
                                {Project.DB.TextString("New Hydrolynx station")},
                                {Project.DB.DateTime(Now)})"

                Project.DB.ExecuteNonQuery(tran, sql)
            Next

            For Each PCode In lstPCodes
                sql = $"IF NOT EXISTS (SELECT * FROM {Project.FullTableName(clsProject.enumTableTypes.PCodes)} WHERE PCode={Project.DB.TextString(PCode, 10)}) 
                            INSERT INTO {Project.FullTableName(clsProject.enumTableTypes.PCodes)} (PCode, Anal_Name, Create_Update) 
                            VALUES (
                                {Project.DB.TextString(PCode, 10)},
                                {Project.DB.TextString("New Hydrolynx parameter")},
                                {Project.DB.DateTime(Now)})"

                Project.DB.ExecuteNonQuery(tran, sql)
            Next

            tran.Commit()

            Project.UpdateTrackingRecord(WRDBTable, Project.GetRecordCount(WRDBTable), True)

            AppendLog($"Imported {NumRecords} new records from {ImportFolder} to {WRDBProjectName}/{WRDBTable}.")

        Catch ex As Exception

            If tran IsNot Nothing Then tran.Rollback()
            ErrorMsg(, ex)

        Finally

            If tran IsNot Nothing Then tran.Dispose()

        End Try

    End Sub

End Class

Public Class clsImportSCADA : Inherits clsImport
    Public Property DatabaseType As WRDB.DBProviders.clsDB.enumDBType = DBProviders.clsDB.enumDBType.SQL_Server
    Public Property ServerName As String = ""
    Public Property DatabaseName As String = ""
    Public Property UserName As String = ""
    Public Property Password As String = ""
    Public Property SourceTable As String = ""
    Private Const MinAllowedResult As Double = 0.0
    Private Const MaxAllowedResult As Double = Double.MaxValue

    Public Sub New(ConnectionName As String)
        MyBase.New()
        Me.ConnectionName = ConnectionName
        Me.ConnectionType = enumConnectionType.SCADA
    End Sub

    Public Overrides Sub Load(SectionName As String)
        MyBase.Load(SectionName)
        DatabaseType = GetKey(SectionName, enumTaskKey.DatabaseType, "")
        ServerName = GetKey(SectionName, enumTaskKey.ServerName, "")
        DatabaseName = GetKey(SectionName, enumTaskKey.DatabaseName, "")
        UserName = GetKey(SectionName, enumTaskKey.UserName, "")
        Password = GetKey(SectionName, enumTaskKey.Password, "")
        SourceTable = GetKey(SectionName, enumTaskKey.SourceTable, "")
    End Sub

    Public Overrides Sub Save(SectionName As String)
        MyBase.Save(SectionName)
        SetKey(SectionName, enumTaskKey.DatabaseType, DatabaseType)
        SetKey(SectionName, enumTaskKey.ServerName, ServerName)
        SetKey(SectionName, enumTaskKey.DatabaseName, DatabaseName)
        SetKey(SectionName, enumTaskKey.UserName, UserName)
        SetKey(SectionName, enumTaskKey.Password, Password)
        SetKey(SectionName, enumTaskKey.SourceTable, SourceTable)
    End Sub

    Private Function SCADADB() As WRDB.DBProviders.clsDB
        Dim DB As WRDB.DBProviders.clsDB = Nothing

        Select Case Me.DatabaseType
            Case clsDB.enumDBType.Firebird
                'database files will be stored on server in the root directory, and given the extension fdb
                DatabaseName = String.Format("\{0}.fdb", DatabaseName)
        End Select

        DB = clsDB.GetDataProvider(DatabaseType, ServerName, DatabaseName, UserName, Password, 60)
        If DB Is Nothing Then
            Throw New DbSqlException("Unable to connect to database: " & DatabaseName)
        End If

        If Not DB.TestConnectionQuick(5) Then
            Throw New DbSqlException("Unable to connect to database: " & DatabaseName)
        End If

        Return DB

    End Function

    Public Overrides Sub BuildMapping(MappingFile As String)

        MyBase.BuildMapping(MappingFile)

        'retrieve available columns from external table

        If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "Connecting to SCADA and logging in..." : Application.DoEvents()

        Dim DB As WRDB.DBProviders.clsDB = Nothing

        Try
            DB = SCADADB()
        Catch ex As Exception
            AppendLog("Unable to connect to SCADA; check username/password.")
            SetTrayText("An error occurred; check your settings and log file.")
            Exit Sub
        End Try

        'retrieve a list of all columns in export table
        'table format is as follows:
        'Col 1: TIME (datetime field)
        'Col 2: (TAGNAME)-VAL (value in data stream)
        'Col 3: (TAGNAME)-VAL-Q (quality code)
        'Col 4,5, etc.: Pairs of -VAL and -VAL-Q columns for each tag
        'TAGNAMES look like this: MTHP.RWCTur

        If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "Acquiring SCADA exported column names..." : Application.DoEvents()

        Dim lstColNames As List(Of String) = Nothing

        If Me.DatabaseType = clsDB.enumDBType.SQL_Server Then
            'note: cannot call GetColumnList because of bug in SQL Server which will not return column names containing periods
            lstColNames = New List(Of String)
            Dim dr As Data.Common.DbDataReader = DB.ExecuteReader($"SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='{SourceTable}' AND TABLE_SCHEMA='dbo'")
            While dr IsNot Nothing AndAlso dr.Read
                lstColNames.Add(dr.GetString(0))
            End While
            dr.Close()
        Else
            lstColNames = DB.GetColumnList(SourceTable)
        End If

        Dim lstTagNames As New List(Of String)

        'make field names upper case
        For i = 1 To lstColNames.Count - 1 Step 2
            lstTagNames.Add(lstColNames(i).ToUpper.Replace("-Q", "").Replace("-VAL", ""))
        Next

        lstTagNames.Sort()

        If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "Initializing mapping settings..." : Application.DoEvents()

        If Not LoadMapping() Then Exit Sub

        With New frmBuildMapping
            .dictMapping = dictMapping 'so can display mapped station id and pcode when selected
            .Project = New clsProject(WRDBProjectName)
            With .TreeView1
                .CheckBoxes = True
                For Each tag In lstTagNames
                    With .Nodes.Add(tag)
                        .Checked = dictMapping.ContainsKey(tag)
                    End With
                Next
                .SelectedNode = Nothing
                .CollapseAll()
            End With

            If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "" : Application.DoEvents()

            If .ShowDialog = DialogResult.OK Then SaveMapping()

        End With
        If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = ""
    End Sub

    Public Overrides Sub Import()

        Dim tran As Common.DbTransaction = Nothing

        Try

            MyBase.Import()

            If Not My.Computer.FileSystem.FileExists(MappingFile) Then
                AppendLog("Unable to find specified import mapping file {0}.", MappingFile)
                SetTrayText("An error occurred; check your settings and log file.")
                Exit Sub
            End If

            If Not LoadMapping() Then Exit Sub

            Try

                'table format is as follows:
                'Col 1: TIME (datetime field)
                'Col 2: (TAGNAME)-VAL (value in data stream)
                'Col 3: (TAGNAME)-VAL-Q (quality code)
                'Col 4,5, etc.: Pairs of -VAL and -VAL-Q columns for each tag
                'TAGNAMES look like this: MTHP.RWCTur

                Dim DB As WRDB.DBProviders.clsDB = SCADADB()

                'make sure the table exists
                If Not DB.GetTableList.Contains(SourceTable) Then
                    WarningMsg($"Specified SCADA source table ({SourceTable}) was not found.")
                    Exit Sub
                End If

                Progress(Nothing, -1, -1, $"Acquiring SCADA data from {SourceTable}...")

                Dim dt As DataTable = Nothing
                Dim sql As String = $"SELECT * FROM {DB.AddBrackets(SourceTable)}
                                      WHERE TIME BETWEEN {DB.DateTime(FirstImportDate)}
                                                     AND {DB.DateTime(LastImportDate)} 
                                      ORDER BY TIME"
                If Not DB.AddTable(dt, sql) Then
                    Throw New DbSqlException("Unable to read SCADA table: " & SourceTable)
                End If

                'to import the data open mapping file which must contain the following columns (tab-separated):
                'Col1: TAGNAME
                'Col2: Station_ID
                'Col3: PCode

                'only tags identified in this mapping file will be imported; if a tag is found in mapping
                'file but not in table, it will be ignored but a warning will be issued

                Dim recid As Integer = TestNull(Project.DB.ExecuteScalar("SELECT MAX(RecID) FROM {0}", Project.DB.AddBrackets(WRDBTable)), 0)

                tran = Project.DB.BeginTransaction

                Dim NumRecords As Integer = 0
                Dim NumProcessed As Integer = 0 'a counter for the number of items examined for progress only
                Dim NumLines As Integer = dictMapping.Count
                Dim TotalRecords As Integer = NumLines * dt.Rows.Count

                For Each kv In dictMapping

                    Dim TagName As String = kv.Key
                    Dim StationID As String = kv.Value.StationID
                    Dim PCode As String = kv.Value.PCode
                    Dim Depth As Double = kv.Value.Depth
                    Dim LCode As String = kv.Value.LCode

                    'delete recent records in WRDB table to avoid duplicates
                    Project.DB.ExecuteNonQuery(tran, $"DELETE FROM {Project.FullTableName(WRDBTable)} 
                                                        WHERE Station_ID={Project.DB.TextString(StationID)}
                                                        AND PCode={Project.DB.TextString(PCode)}
                                                        AND Date_Time BETWEEN {Project.DB.DateTime(FirstImportDate)} 
                                                                          AND {Project.DB.DateTime(LastImportDate)}")

                    'import from each row of data table; currently uses current login but may want to leave Owner as NULL so everyone can see (no one claims ownership)

                    If dt.Columns.Contains(TagName & "-VAL") AndAlso dt.Columns.Contains(TagName & "-VAL-Q") Then
                        For Each dr As DataRow In dt.Rows
                            Progress(Nothing, NumProcessed, TotalRecords, $"Importing SCADA tag {TagName} To {StationID}/{PCode}...")
                            Dim time As DateTime = TestNull(dr("TIME"), Date.MinValue)
                            If time = Date.MinValue Then Continue For
                            Dim qcode As String = TestNull(dr(TagName & "-VAL-Q"), "").ToString.ToUpper
                            If qcode.Length > 1 Then qcode = qcode.Substring(0, 1)
                            Dim result As Double = TestNull(dr(TagName & "-VAL"), Double.MinValue)
                            If time <> Date.MinValue AndAlso qcode = "1" AndAlso result >= MinAllowedResult AndAlso result <= MaxAllowedResult Then
                                'todo: confirm with vendor that qcode of 1 is the only one that should be imported
                                recid += 1
                                sql = $"INSERT INTO {Project.FullTableName(WRDBTable)} 
                                        (RecID, Station_ID, PCode, Date_Time, QCode, Result, Depth_M, LCode, Create_Update, Owner) 
                                        VALUES (
                                            {Project.DB.Numeric(recid)}, 
                                            {Project.DB.TextString(StationID.ToUpper, 25)}, 
                                            {Project.DB.TextString(PCode.ToUpper, 10)}, 
                                            {DB.DateTime(dr("TIME"))}, 
                                            {Project.DB.TextString(qcode, 3)}, 
                                            {Project.DB.Numeric(result)}, 
                                            {Project.DB.Numeric(Depth)}, 
                                            {Project.DB.TextString(LCode.ToUpper, 3)}, 
                                            {DB.DateTime(Now)}, 
                                            {Project.DB.TextString(clsApplicationSettings.UserName, 3)})"
                                Project.DB.ExecuteNonQuery(tran, sql)
                                Application.DoEvents()
                                NumRecords += 1
                                If ImportProgressForm.IsCancelled Then
                                    AppendLog($"Import cancelled by user while importing {kv.Key} to {WRDBTable}.")
                                    ErrorsOccurred = True
                                    tran.Rollback()
                                    Exit Sub
                                End If
                            End If
                            NumProcessed += 1
                        Next
                    End If

                    'update the support tables

                    sql = $"IF NOT EXISTS (SELECT * FROM {Project.FullTableName(clsProject.enumTableTypes.Stations)} Stations WHERE Station_ID={Project.DB.TextString(StationID, 25)}) 
                            INSERT INTO {Project.FullTableName(clsProject.enumTableTypes.Stations)} (Station_ID, Station_Name, Create_Update) 
                            VALUES (
                                {Project.DB.TextString(StationID, 25)},
                                {Project.DB.TextString("New SCADA station")},
                                {Project.DB.DateTime(Now)})"

                    Project.DB.ExecuteNonQuery(tran, sql)

                    sql = $"IF NOT EXISTS (SELECT * FROM {Project.FullTableName(clsProject.enumTableTypes.PCodes)} WHERE PCode={Project.DB.TextString(PCode, 10)}) 
                            INSERT INTO {Project.FullTableName(clsProject.enumTableTypes.PCodes)} (PCode, Anal_Name, Create_Update) 
                            VALUES (
                                {Project.DB.TextString(PCode, 10)},
                                {Project.DB.TextString("New SCADA parameter")},
                                {Project.DB.DateTime(Now)})"

                    Project.DB.ExecuteNonQuery(tran, sql)

                Next

                If DeleteBeforeDate <> Date.MinValue Then
                    Project.DB.ExecuteNonQuery(tran, $"DELETE FROM {Project.FullTableName(WRDBTable)} 
                                                       WHERE Date_Time<{Project.DB.DateTime(DeleteBeforeDate)}")
                End If

                tran.Commit()
                Project.UpdateTrackingRecord(WRDBTable, Project.GetRecordCount(WRDBTable), True)

                AppendLog($"Imported {NumRecords} new records to {WRDBProjectName}/{WRDBTable}.")

            Catch ex As Exception
                AppendLog("Error occurred while trying to import: {0}.", ex.ToString)
                ErrorsOccurred = True

            Finally
                If Project IsNot Nothing Then RemoveHandler Project.Progress, AddressOf Progress
            End Try


        Catch ex As Exception

            If tran IsNot Nothing Then tran.Rollback()
            ErrorMsg(, ex)

        Finally

            If tran IsNot Nothing Then tran.Dispose()

        End Try

    End Sub

End Class

Public Class clsImportAquarius : Inherits clsImport

    Public Enum enumAPIVer
        V1
        V2
    End Enum

    Public Property APIVer As enumAPIVer = enumAPIVer.V1
    Public Property ServerName As String = ""
    Public Property UserName As String = ""
    Public Property Password As String = ""
    Public Property Resample As Boolean = False
    Public Property ResampleInterval As Integer = 60

    Private dictLocations As SortedDictionary(Of String, clsAquariusLocation) = Nothing
    Private dictParameters As SortedDictionary(Of String, clsAquariusParameter) = Nothing

    Private Function baseURL() As String
        Return $"http://{ServerName}/AQUARIUS/Publish/V2/"
    End Function

    Public Sub New(ConnectionName As String)
        MyBase.New()
        Me.ConnectionName = ConnectionName
        Me.ConnectionType = enumConnectionType.Aquarius
    End Sub

    Public Overrides Sub Load(SectionName As String)
        MyBase.Load(SectionName)
        APIVer = GetKey(SectionName, enumTaskKey.APIVer, enumAPIVer.V1)
        ServerName = GetKey(SectionName, enumTaskKey.ServerName, "")
        UserName = GetKey(SectionName, enumTaskKey.UserName, "")
        Password = GetKey(SectionName, enumTaskKey.Password, "")
        Resample = GetKey(SectionName, enumTaskKey.Resample, True)
        ResampleInterval = GetKey(SectionName, enumTaskKey.ResampleInterval, 60)
    End Sub

    Public Overrides Sub Save(SectionName As String)
        MyBase.Save(SectionName)
        SetKey(SectionName, enumTaskKey.APIVer, APIVer)
        SetKey(SectionName, enumTaskKey.ServerName, ServerName)
        SetKey(SectionName, enumTaskKey.UserName, UserName)
        SetKey(SectionName, enumTaskKey.Password, Password)
        SetKey(SectionName, enumTaskKey.Resample, Resample)
        SetKey(SectionName, enumTaskKey.ResampleInterval, ResampleInterval)
    End Sub

    Private Class clsAquariusLocation
        Property ID As String
        Property Name As String
        Property Identifier As String
        Property Latitude As Double
        Property Longitude As Double
        Property Elevation As Double
        Property TypeName As String
        Property Description As String
    End Class

    Private Class clsAquariusParameter
        Property ID As String
        Property DisplayID As String
        Property Name As String
        Property Units As String
    End Class

    Private Class clsAquariusDataset
        Property LocationID As String
        Property DataID As String
        Property ParameterID As String
        Property Units As String
        Property TotalSamples As Integer
    End Class

    Private Class clsAquariusGrade
        Property Code As String
        Property TypeCode As String
        Property Description As String
    End Class

    Private Class clsAquariusApproval
        Property Code As String
        Property Description As String
    End Class

    ''' <summary>
    ''' Retrieve dictionary of locations using API V1
    ''' </summary>
    ''' <param name="client">SOAP client</param>
    ''' <param name="token">Auth token</param>
    ''' <returns></returns>
    Private Function Locations(client As AquariusPublishService.AquariusPublishServiceClient, token As String) As SortedDictionary(Of String, clsAquariusLocation)
        'retrieve a list of all Locations and store in dictionary so can update the Stations table if necessary
        'the csv list has these columns: 
        'LocationID, LOCATIONFOLDERID, LASTMODIFIED, LOCATIONNAME, DESCRIPTION, IDENTIFIER, LOCATIONTYPEID, LATITUDE, LONGITUDE, SRID, ELEVATIONUNITS, ELEVATION, UTCOFFSET, TimeZone, DEFAULTROLEID, AQUSERDATA, LOCATIONTYPENAME, ATTRIBUTETABLENAME, LOCATIONPATH

        Dim dict As New SortedDictionary(Of String, clsAquariusLocation)

        Dim tfp As New FileIO.TextFieldParser(New IO.StringReader(client.GetLocations(token, "*")))
        tfp.SetDelimiters(",")
        tfp.HasFieldsEnclosedInQuotes = True

        tfp.ReadLine() 'skip header line

        While Not tfp.EndOfData
            Dim ar() As String = tfp.ReadFields
            If ar Is Nothing OrElse ar.Length <> 19 Then Throw New IndexOutOfRangeException("19 values were Not returned On line " & tfp.LineNumber)
            Dim lat As Double = Double.NaN : If Not String.IsNullOrEmpty(ar(7)) Then lat = Double.Parse(ar(7), Globalization.CultureInfo.InvariantCulture)
            Dim lon As Double = Double.NaN : If Not String.IsNullOrEmpty(ar(8)) Then lon = Double.Parse(ar(8), Globalization.CultureInfo.InvariantCulture)
            Dim elv As Double = Double.NaN : If Not String.IsNullOrEmpty(ar(11)) Then elv = Double.Parse(ar(11), Globalization.CultureInfo.InvariantCulture)
            dict.Add(ar(5), New clsAquariusLocation With {
                     .ID = ar(0),
                     .Name = ar(3),
                     .Identifier = ar(5),
                     .Latitude = lat,
                     .Longitude = lon,
                     .Elevation = elv,
                     .TypeName = ar(16)})
        End While

        tfp.Close()
        tfp.Dispose()

        Return dict
    End Function

    ''' <summary>
    ''' Retrieve dictionary of locations using API V2
    ''' </summary>
    ''' <param name="wc">Web client</param>
    ''' <returns></returns>
    Private Function Locations(wc As WebClientEx) As SortedDictionary(Of String, clsAquariusLocation)

        Dim dict As New SortedDictionary(Of String, clsAquariusLocation)

        Dim ds As DataSet = DownloadXML(wc, $"{baseURL()}GetLocationDescriptionList?Format= Xml")

        With ds.Tables("LocationDescription")
            For Each r As DataRow In .Rows
                dict.Add(r("Identifier"), New clsAquariusLocation With {.ID = r("Identifier"), .Name = r("Name")})
            Next
        End With

        For Each kv As KeyValuePair(Of String, clsAquariusLocation) In dict

            Try
                ds = DownloadXML(wc, $"{baseURL()}GetLocationData?LocationIdentifier={kv.Key}&Format=xml")

                With ds.Tables("LocationDataServiceResponse")
                    For Each r As DataRow In .Rows
                        With dict(kv.Key)
                            .Latitude = r("Latitude")
                            .Longitude = r("Longitude")
                            .Elevation = r("Elevation")
                            .TypeName = r("LocationType")
                            .Description = r("Description")
                        End With
                    Next
                End With
            Catch ex As Exception
                AppendLog($"Unable To parse XML table for station {kv.Key}; remove unnecessary remarks due to bug in API V2.")
                ErrorsOccurred = True
            Finally
                ds.Dispose()
            End Try
        Next

        Return dict
    End Function

    ''' <summary>
    ''' Retrieve dictionary of parameters using API V1
    ''' </summary>
    ''' <param name="client">SOAP client</param>
    ''' <param name="token">Auth token</param>
    ''' <returns></returns>
    Private Function Parameters(client As AquariusPublishService.AquariusPublishServiceClient, token As String) As SortedDictionary(Of String, clsAquariusParameter)
        'retrieve a list of all parameters and store in dictionary so can update the PCodes table if necessary
        'the csv list has these columns: 
        'Parameter, DisplayId, Name, Interpolation, Unit
        'Note that key to dictionary is Display ID (stage) not ID (HG)

        Dim dict As New SortedDictionary(Of String, clsAquariusParameter)

        Dim tfp As New FileIO.TextFieldParser(New IO.StringReader(client.GetParameterList(token)))
        tfp.SetDelimiters(",")
        tfp.HasFieldsEnclosedInQuotes = True

        tfp.ReadLine() 'skip header line

        While Not tfp.EndOfData
            Dim ar() As String = tfp.ReadFields
            If ar Is Nothing OrElse ar.Length <> 5 Then Throw New IndexOutOfRangeException("5 values were Not returned On line " & tfp.LineNumber)
            dict.Add(ar(1), New clsAquariusParameter With {
                     .ID = ar(0),
                     .DisplayID = ar(1),
                     .Name = ar(2),
                     .Units = ar(4)})
        End While

        tfp.Close()
        tfp.Dispose()

        Return dict
    End Function

    ''' <summary>
    ''' Retrieve dictionary of parameters using API V2
    ''' </summary>
    ''' <param name="wc">Webclient</param>
    ''' <returns></returns>
    Private Function Parameters(wc As WebClientEx) As SortedDictionary(Of String, clsAquariusParameter)
        'retrieve a dataset of all parameters and store in dictionary so can update the PCodes table if necessary
        'the dataset has a table ParameterMetadata with these fields: 
        'Identifier, DisplayName, InterpolationType, UnitGroupIdentifier, UnitIdentifier
        'Note that Identifier is DisplayID (Stage) not ID (HG)

        Dim dict As New SortedDictionary(Of String, clsAquariusParameter)

        Dim ds As DataSet = DownloadXML(wc, $"{baseURL()}GetParameterList?Format=xml")

        With ds.Tables("ParameterMetadata")
            For Each r As DataRow In .Rows
                dict.Add(r("Identifier"), New clsAquariusParameter With {
                                   .ID = r("Identifier"),
                                   .Name = r("DisplayName"),
                                   .Units = r("UnitIdentifier")})
            Next
        End With
        ds.Dispose()
        Return dict
    End Function

    ''' <summary>
    ''' Retrieve dictionary of grades using API V1
    ''' </summary>
    ''' <param name="client">SOAP client</param>
    ''' <param name="token">Auth token</param>
    ''' <returns></returns>
    Private Function Grades(client As AquariusPublishService.AquariusPublishServiceClient, token As String) As SortedDictionary(Of String, clsAquariusGrade)
        'retrieve a list of all grades and store in dictionary so can update the SCodes table if necessary
        'the csv list has these columns: 
        'Code,TypeCode,Description,Color

        Dim dict As New SortedDictionary(Of String, clsAquariusGrade)

        Dim tfp As New FileIO.TextFieldParser(New IO.StringReader(client.GetGradeList(token)))
        tfp.SetDelimiters(",")
        tfp.HasFieldsEnclosedInQuotes = True

        Dim line As String = tfp.ReadLine() 'skip header line

        While Not tfp.EndOfData
            Dim ar() As String = tfp.ReadFields
            If ar Is Nothing OrElse ar.Length <> 4 Then Throw New IndexOutOfRangeException("4 values were Not returned On line " & tfp.LineNumber)
            dict.Add(ar(0), New clsAquariusGrade With {
                     .Code = ar(0),
                     .TypeCode = ar(1),
                     .Description = ar(2)})
        End While

        tfp.Close()
        tfp.Dispose()

        Return dict
    End Function

    ''' <summary>
    ''' Retrieve dictionary of grades using API V2
    ''' </summary>
    ''' <param name="wc">Webclient</param>
    ''' <returns></returns>
    Private Function Grades(wc As WebClientEx) As SortedDictionary(Of String, clsAquariusGrade)
        'retrieve a list of all grades and store in dictionary so can update the SCodes table if necessary

        Dim dict As New SortedDictionary(Of String, clsAquariusGrade)

        Dim ds As DataSet = DownloadXML(wc, $"{baseURL()}GetGradeList?Format=xml")

        With ds.Tables("GradeMetadata")
            For Each r As DataRow In .Rows
                dict.Add(r("Identifier"), New clsAquariusGrade With {
                                   .Code = r("Identifier"),
                                   .TypeCode = r("DisplayName"),
                                   .Description = r("Description")})
            Next
        End With
        ds.Dispose()
        Return dict
    End Function

    ''' <summary>
    ''' Retrieve dictionary of approvals using API V1
    ''' </summary>
    ''' <param name="client">SOAP client</param>
    ''' <param name="token">Auth token</param>
    ''' <returns></returns>
    Private Function Approvals(client As AquariusPublishService.AquariusPublishServiceClient, token As String) As SortedDictionary(Of String, clsAquariusApproval)
        'retrieve a list of all approvals and store in dictionary so can update the QCodes table if necessary
        'the csv list has these columns: 
        'Code,TypeCode,Description,Color

        Dim dict As New SortedDictionary(Of String, clsAquariusApproval)

        Dim tfp As New FileIO.TextFieldParser(New IO.StringReader(client.GetApprovalList(token)))
        tfp.SetDelimiters(",")
        tfp.HasFieldsEnclosedInQuotes = True

        tfp.ReadLine() 'skip header line

        While Not tfp.EndOfData
            Dim ar() As String = tfp.ReadFields
            If ar Is Nothing OrElse ar.Length <> 3 Then Throw New IndexOutOfRangeException("3 values were Not returned On line " & tfp.LineNumber)
            dict.Add(ar(0), New clsAquariusApproval With {
                     .Code = ar(0),
                     .Description = ar(1)})
        End While

        tfp.Close()
        tfp.Dispose()

        Return dict
    End Function

    ''' <summary>
    ''' Retrieve dictionary of approvals using API V2
    ''' </summary>
    ''' <param name="wc">Webclient</param>
    ''' <returns></returns>
    Private Function Approvals(wc As WebClientEx) As SortedDictionary(Of String, clsAquariusApproval)
        'retrieve a list of all approvals and store in dictionary so can update the QCodes table if necessary

        Dim dict As New SortedDictionary(Of String, clsAquariusApproval)

        Dim ds As DataSet = DownloadXML(wc, $"{baseURL()}GetApprovalList?Format=xml")

        With ds.Tables("GradeMetadata")
            For Each r As DataRow In .Rows
                dict.Add(r("Identifier"), New clsAquariusApproval With {
                                   .Code = r("Identifier"),
                                   .Description = r("DisplayName")})
            Next
        End With
        ds.Dispose()
        Return dict
    End Function

    ''' <summary>
    ''' Retrieve dictionary of datasets using API V1
    ''' </summary>
    ''' <param name="client">SOAP client</param>
    ''' <param name="token">Auth token</param>
    ''' <param name="LocationID">ID of location</param>
    ''' <returns></returns>
    Private Function Datasets(client As AquariusPublishService.AquariusPublishServiceClient, token As String, LocationID As String) As SortedDictionary(Of String, clsAquariusDataset)
        'retrieve a list of all parameters and store in dictionary 
        'the csv list has these columns: 
        'LocationId,DataId,Parameter,Unit,Min,Max,Mean,StartTime,StartValue,EndTime,EndValue,TotalGaps,TotalSamples,LastModified

        Dim dict As New SortedDictionary(Of String, clsAquariusDataset)

        'allow reverse lookup from parameterID to DisplayID
        Dim dictParm As New Dictionary(Of String, String)
        For Each kv As KeyValuePair(Of String, clsAquariusParameter) In Parameters(client, token)
            dictParm.Add(kv.Value.ID, kv.Value.DisplayID)
        Next

        'the following statement is timing out for Aquarius V1 only

        Dim tfp As New FileIO.TextFieldParser(New IO.StringReader(client.GetDataSetsList(token, LocationID, "")))
        tfp.SetDelimiters(",")
        tfp.HasFieldsEnclosedInQuotes = True

        tfp.ReadLine() 'skip header line

        While Not tfp.EndOfData
            Dim ar() As String = tfp.ReadFields
            If ar Is Nothing OrElse ar.Length <> 14 Then Throw New IndexOutOfRangeException("14 values were Not returned On line " & tfp.LineNumber)
            Dim DataID As String = ar(1) 'for V1 this is like HG.Telemetria@ABC; for consistency convert to DisplayID like Stage.Telemetria@ABC
            Dim ParmID As String = ar(2)
            DataID = DataID.Replace(ParmID, dictParm(ParmID))
            dict.Add(DataID, New clsAquariusDataset With {
                     .LocationID = ar(0),
                     .DataID = DataID,
                     .ParameterID = ParmID,
                     .Units = ar(3),
                     .TotalSamples = TestNull(ar(12), 0)})
        End While

        tfp.Close()
        tfp.Dispose()

        Return dict
    End Function

    ''' <summary>
    ''' Retrieve dictionary of datasets using API V2
    ''' </summary>
    ''' <param name="wc">Webclient</param>
    ''' <param name="LocationID">ID of location</param>
    ''' <returns></returns>
    Private Function Datasets(wc As WebClientEx, LocationID As String) As SortedDictionary(Of String, clsAquariusDataset)
        'retrieve a list of all parameters and store in dictionary 
        'the csv list has these columns: 
        'LocationId,DataId,Parameter,Unit,Min,Max,Mean,StartTime,StartValue,EndTime,EndValue,TotalGaps,TotalSamples,LastModified

        Dim dict As New SortedDictionary(Of String, clsAquariusDataset)

        Dim ds As DataSet = DownloadXML(wc, $"{baseURL()}GetTimeSeriesDescriptionList?LocationIdentifier={LocationID}&Format=xml")

        With ds.Tables("TimeSeriesDescription")
            For Each r As DataRow In .Rows
                dict.Add(r("Identifier"), New clsAquariusDataset With {
                     .LocationID = r("LocationIdentifier"),
                     .DataID = r("Identifier"),
                     .ParameterID = r("Parameter"),
                     .Units = r("Unit"),
                     .TotalSamples = TestNull(r("NumPoints"), 0)})
            Next
        End With
        ds.Dispose()
        Return dict
    End Function

    ''' <summary>
    ''' Retrieve dictionary of datasets using API V1 for all locations
    ''' </summary>
    ''' <param name="client">SOAP client</param>
    ''' <param name="token">Auth token</param>
    ''' <returns></returns>
    Private Function Datasets(client As AquariusPublishService.AquariusPublishServiceClient, token As String) As SortedDictionary(Of String, clsAquariusDataset)
        'retrieve a list of all parameters and store in dictionary 
        'the csv list has these columns: 
        'LocationId,DataId,Parameter,Unit,Min,Max,Mean,StartTime,StartValue,EndTime,EndValue,TotalGaps,TotalSamples,LastModified

        Dim dict As New SortedDictionary(Of String, clsAquariusDataset)

        'allow reverse lookup from parameterID to DisplayID
        Dim dictParm As New Dictionary(Of String, String)
        For Each kv As KeyValuePair(Of String, clsAquariusParameter) In Parameters(client, token)
            dictParm.Add(kv.Value.ID, kv.Value.DisplayID)
        Next

        Dim tfp As New FileIO.TextFieldParser(New IO.StringReader(client.GetDataSetsList(token, "", "")))
        tfp.SetDelimiters(",")
        tfp.HasFieldsEnclosedInQuotes = True

        tfp.ReadLine() 'skip header line

        While Not tfp.EndOfData
            Dim ar() As String = tfp.ReadFields
            If ar Is Nothing OrElse ar.Length <> 14 Then Throw New IndexOutOfRangeException("14 values were Not returned On line " & tfp.LineNumber)
            Dim DataID As String = ar(1) 'for V1 this is like HG.Telemetria@ABC; for consistency convert to DisplayID like Stage.Telemetria@ABC
            Dim ParmID As String = ar(2)
            DataID = DataID.Replace(ParmID, dictParm(ParmID))
            dict.Add(DataID, New clsAquariusDataset With {
                     .LocationID = ar(0),
                     .DataID = DataID,
                     .ParameterID = ParmID,
                     .Units = ar(3),
                     .TotalSamples = TestNull(ar(12), 0)})
        End While

        tfp.Close()
        tfp.Dispose()

        Return dict
    End Function

    ''' <summary>
    ''' Retrieve dictionary of datasets using API V2 for all locations
    ''' </summary>
    ''' <param name="wc">Webclient</param>
    ''' <returns></returns>
    Private Function Datasets(wc As WebClientEx) As SortedDictionary(Of String, clsAquariusDataset)
        'retrieve a list of all parameters and store in dictionary 
        'the csv list has these columns: 
        'LocationId,DataId,Parameter,Unit,Min,Max,Mean,StartTime,StartValue,EndTime,EndValue,TotalGaps,TotalSamples,LastModified

        Dim dict As New SortedDictionary(Of String, clsAquariusDataset)

        Dim ds As DataSet = DownloadXML(wc, $"{baseURL()}GetTimeSeriesDescriptionList?Format=xml")

        With ds.Tables("TimeSeriesDescription")
            For Each r As DataRow In .Rows
                dict.Add(r("Identifier"), New clsAquariusDataset With {
                     .LocationID = r("LocationIdentifier"),
                     .DataID = r("Identifier"),
                     .ParameterID = r("Parameter"),
                     .Units = r("Unit"),
                     .TotalSamples = TestNull(r("NumPoints"), 0)})
            Next
        End With
        ds.Dispose()

        Return dict
    End Function

    Public Overrides Sub BuildMapping(MappingFile As String)

        Try

            MyBase.BuildMapping(MappingFile)

            'retrieve location and dataset information

            Dim client As AquariusPublishService.AquariusPublishServiceClient = Nothing
            Dim token As String = ""
            Dim wc As WebClientEx = Nothing

            'following made static so don't have to perform lengthy query to retrieve datasets; takes some memory but rarely used
            Static dictDatasets As New SortedDictionary(Of String, SortedDictionary(Of String, clsAquariusDataset))

            If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "Connecting to Aquarius and logging in..." : Application.DoEvents()

            If APIVer = enumAPIVer.V1 Then

                '1. connect to publish service
                client = New AquariusPublishService.AquariusPublishServiceClient("BasicHttpBinding_IAquariusPublishService", $"http://{ServerName}/AQUARIUS/Publish/AquariusPublishService.svc")


                '2. obtain the authorization token needed for all calls
                Try
                    token = client.GetAuthToken(UserName, Password)
                Catch ex As Exception
                    WarningMsg("Unable to connect to Aquarius; check server name and username/password.")
                    ErrorsOccurred = True
                    Exit Sub
                End Try

                '3. retrieve a list of all Locations and store in dictionary so can update the Stations table if necessary

                If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "Acquiring Aquarius locations..." : Application.DoEvents()
                dictLocations = Locations(client, token)

                '4. retrieve a list of all datasets associated with each location

                If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = $"Acquiring Aquarius datasets..." : Application.DoEvents()

                If dictDatasets.Count = 0 Then
                    Dim cnt As Integer = 0
                    For Each kv As KeyValuePair(Of String, clsAquariusLocation) In dictLocations
                        cnt += 1
                        Dim pct As Integer = cnt * 100 / dictLocations.Count
                        If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = $"Acquiring Aquarius datasets {pct}%..." : Application.DoEvents()
                        dictDatasets.Add(kv.Key, Datasets(client, token, kv.Key))
                    Next
                End If

            Else 'use version 2

                wc = New WebClientEx
                wc.Timeout = 5 * 60 * 1000

                '1. obtain the authorization token needed for all calls (this sets a cookie so CookieContainer must have been created)

                Try
                    wc.DownloadData($"{baseURL()}GetAuthToken?userName={UserName}&encryptedPassword={Password}")
                Catch ex As Net.WebException
                    WarningMsg("Unable to connect to Aquarius; check server name and username/password.")
                    ErrorsOccurred = True
                    Exit Sub
                End Try

                '2. retrieve a dataset of all Locations and store in dictionary so can update the Stations table if necessary

                If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "Acquiring Aquarius locations..." : Application.DoEvents()
                dictLocations = Locations(wc)

                '3. retrieve a list of all datasets associated with each location

                If dictDatasets.Count = 0 Then
                    Dim cnt As Integer = 0
                    For Each kv As KeyValuePair(Of String, clsAquariusLocation) In dictLocations
                        cnt += 1
                        Dim pct As Integer = cnt * 100 / dictLocations.Count
                        If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = $"Acquiring Aquarius datasets {pct}%..." : Application.DoEvents()
                        dictDatasets.Add(kv.Key, Datasets(wc, kv.Key))
                    Next
                End If

            End If

            If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "Initializing mapping settings..." : Application.DoEvents()

            If Not LoadMapping() Then Exit Sub

            With New frmBuildMapping
                .dictMapping = dictMapping 'so can display mapped station id and pcode when selected
                .Project = New clsProject(WRDBProjectName)
                With .TreeView1
                    .CheckBoxes = True
                    For Each kv In dictDatasets
                        With .Nodes.Add($"{kv.Key} - {dictLocations(kv.Key).Name}")
                            For Each kv2 As KeyValuePair(Of String, clsAquariusDataset) In kv.Value
                                With .Nodes.Add(kv2.Key)
                                    .Checked = dictMapping.ContainsKey(kv2.Key)
                                End With
                            Next
                        End With
                    Next
                    .SelectedNode = Nothing
                    .CollapseAll()
                End With

                If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = "" : Application.DoEvents()

                If .ShowDialog = DialogResult.OK Then SaveMapping()

            End With

        Catch ex As Exception

            ErrorMsg(, ex)

        Finally

            If ImportForm IsNot Nothing Then ImportForm.lblProgress.Text = ""

        End Try
    End Sub

    Public Overrides Sub Import()
        Const ImportRawData As Boolean = False 'only for testing!

        Dim tran As Common.DbTransaction = Nothing

        Try

            MyBase.Import()

            If Not My.Computer.FileSystem.FileExists(MappingFile) Then
                AppendLog("Unable To find specified import mapping file {0}.", MappingFile)
                SetTrayText("An error occurred; check your settings and log file.")
                Exit Sub
            End If

            Try

                'to import the data open mapping file which must contain the following columns (tab-separated):
                'Col1: Aquarius Dataset ID (use same format as in Springboard)
                'Col2: WRDB Station_ID
                'Col3: WRDB PCode

                'only locations/parameters identified in this mapping file will be imported; if a tag is found in mapping
                'file but not in table, it will be ignored but a warning will be issued
                'if either WRDB items are blank, will use truncated Aquarius IDs

                Dim recid As Integer = TestNull(Project.DB.ExecuteScalar("Select MAX(RecID) FROM {0}", Project.DB.AddBrackets(WRDBTable)), 0)

                tran = Project.DB.BeginTransaction

                'retrieve location and parameter information

                Dim client As AquariusPublishService.AquariusPublishServiceClient = Nothing
                Dim token As String = ""
                Dim wc As WebClientEx = Nothing
                Dim dictParmDisplayID As Dictionary(Of String, String) = Nothing

                Progress(Nothing, -1, -1, "Connecting to Aquarius...")

                If APIVer = enumAPIVer.V1 Then

                    '1. connect to publish service
                    client = New AquariusPublishService.AquariusPublishServiceClient("BasicHttpBinding_IAquariusPublishService", $"http://{ServerName}/AQUARIUS/Publish/AquariusPublishService.svc")

                    '2. obtain the authorization token needed for all calls
                    Try
                        token = client.GetAuthToken(UserName, Password)
                    Catch ex As Exception
                        AppendLog("Invalid Aquarius username/password.")
                        ErrorsOccurred = True
                        Exit Sub
                    End Try

                    Progress(Nothing, -1, -1, "Getting location list...")

                    '3. retrieve a list of all Locations and store in dictionary so can update the Stations table if necessary
                    dictLocations = Locations(client, token)

                    Progress(Nothing, -1, -1, "Getting parameter list...")

                    '4. retrieve a list of all parameters and store in dictionary so can update the PCodes table if necessary
                    dictParameters = Parameters(client, token)

                    'allow reverse lookup from parameterID to DisplayID
                    dictParmDisplayID = New Dictionary(Of String, String)
                    For Each kv As KeyValuePair(Of String, clsAquariusParameter) In dictParameters
                        dictParmDisplayID.Add(kv.Value.ID, kv.Value.DisplayID)
                    Next

                Else 'use version 2

                    wc = New WebClientEx
                    wc.Timeout = 5 * 60 * 1000

                    '1. obtain the authorization token needed for all calls (this sets a cookie so CookieContainer must have been created)

                    Try
                        wc.DownloadData($"{baseURL()}GetAuthToken?userName={UserName}&encryptedPassword={Password}")
                    Catch ex As Exception
                        AppendLog("Invalid Aquarius username/password.")
                        ErrorsOccurred = True
                        Exit Sub
                    End Try

                    Progress(Nothing, -1, -1, "Getting location list...")

                    '2. retrieve a dataset of all Locations and store in dictionary so can update the Stations table if necessary
                    dictLocations = Locations(wc)

                    Progress(Nothing, -1, -1, "Getting parameter list...")

                    '3. retrieve a dataset of all parameters and store in dictionary so can update the PCodes table if necessary
                    dictParameters = Parameters(wc)

                End If

                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                ' Now import the time series data for each mapped dataset
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

                Dim NumRecords As Integer = 0

                If Not LoadMapping() Then Exit Sub
                Dim LineNum As Integer = 0
                Dim Numlines As Integer = dictMapping.Count

                For Each kv In dictMapping

                    Dim DatasetID As String = kv.Key  'like Display ID.Telemetry@ABC
                    Dim LocationID As String = DatasetID.Split("@")(1) 'location code
                    Dim DisplayID As String = DatasetID.Split(".")(0) 'this should be the Display ID (Stage rather than HG); will convert below
                    Dim ParmID As String = dictParameters(DisplayID).ID 'this is ID used in API
                    Dim StationID As String = kv.Value.StationID
                    Dim PCode As String = kv.Value.PCode
                    Dim Depth As Double = kv.Value.Depth
                    Dim LCode As String = kv.Value.LCode
                    Dim LastCumPrecip As Double = 0

                    Progress(Nothing, LineNum, Numlines, $"Importing Aquarius {LocationID}/{ParmID} to WRDB {StationID}/{PCode}...")

                    'delete recent records in WRDB table to avoid duplicates
                    Project.DB.ExecuteNonQuery(tran, $"DELETE FROM {Project.FullTableName(WRDBTable)} 
                                                        WHERE Station_ID={Project.DB.TextString(StationID)}
                                                        AND PCode={Project.DB.TextString(PCode)}
                                                        AND Date_Time BETWEEN {Project.DB.DateTime(FirstImportDate)} 
                                                                          AND {Project.DB.DateTime(LastImportDate)}")

                    LineNum += 1

                    'import from each row of mapping file; currently uses current login but may want to leave Owner as NULL so everyone can see (no one claims ownership)

                    If APIVer = enumAPIVer.V1 Then

                        Dim DatasetIDAPI As String = DatasetID.Replace(DisplayID & ".", ParmID & ".") 'convert Stage.Datalogger@ABC to HG.Datalogger@ABC
                        'retrieve the data
                        'the returned csv contains these columns:
                        'Time, Parameter, LocationId, DataId, NumRanges, NumPoints, PublishView, QueryFrom, QueryTo, ChangesSince, AsAtTime
                        '(metadata)
                        'RangeNumber,StartTime,EndTime,NumPoints
                        '(table1 data ranges)
                        'RangeNumber,Time,Value,Quality,Interpolation,Approval
                        '(table2 data)

                        Dim tfpTS As FileIO.TextFieldParser = Nothing

                        'can only retrieve about 1000 observations at a time without running out of memory
                        'although the server could be reconfigured to increase that limit, is probably better
                        'to just import in batches.

                        'estimated number of points (if not resampled, assume 10 minute interval)
                        Dim interval As Integer = IIf(Resample, ResampleInterval, 10)
                        Dim numvals As Integer = LastImportDate.Subtract(FirstImportDate).TotalMinutes / interval

                        Dim pointwindow As Integer = 1000 'may want this to be user configurable
                        Dim pmax As Integer = Int((numvals - 1) / pointwindow)

                        For p As Integer = 0 To pmax

                            Dim dt1 As Date = FirstImportDate.AddMinutes(p * pointwindow * interval)
                            Dim dt2 As Date = dt1.AddMinutes(pointwindow * interval).AddSeconds(-1)
                            If dt2 > LastImportDate Then dt2 = LastImportDate

                            Try
                                If ImportRawData Then
                                    tfpTS = New FileIO.TextFieldParser(New IO.StringReader(
                                            client.GetTimeSeriesRawData(
                                            token,
                                            DatasetIDAPI,
                                            "",
                                            dt1.ToString("yyyy-MM-ddTHH:mm:ss.fffzzz"),
                                            dt2.ToString("yyyy-MM-ddTHH:mm:ss.fffzzz"),
                                            "",
                                            "")))
                                ElseIf Resample Then
                                    tfpTS = New FileIO.TextFieldParser(New IO.StringReader(
                                            client.GetTimeSeriesDataResampled(
                                            token,
                                            DatasetIDAPI,
                                            "",
                                            dt1.ToString("yyyy-MM-ddTHH:mm:ss.fffzzz"),
                                            dt2.ToString("yyyy-MM-ddTHH:mm:ss.fffzzz"),
                                            ResampleInterval,
                                            dt1.ToString("yyyy-MM-ddTHH:mm:ss.fffzzz"))))
                                Else
                                    tfpTS = New FileIO.TextFieldParser(New IO.StringReader(
                                            client.GetTimeSeriesData(
                                            token,
                                            DatasetIDAPI,
                                            "",
                                            dt1.ToString("yyyy-MM-ddTHH:mm:ss.fffzzz"),
                                            dt2.ToString("yyyy-MM-ddTHH:mm:ss.fffzzz"),
                                            "",
                                            "")))
                                End If
                            Catch ex As Exception
                                If Not ex.Message.Contains("RESAMPLE_CORR") Then 'this error is thrown when no data are available; not a real error
                                    AppendLog($"Exception occurred while downloading data set {kv.Key}: {ex.Message}")
                                    ErrorsOccurred = True
                                End If
                                Exit For
                            End Try

                            tfpTS.SetDelimiters(",")
                            tfpTS.HasFieldsEnclosedInQuotes = True
                            tfpTS.ReadLine() 'skip header line before metadata

                            Dim arTS() As String = tfpTS.ReadFields
                            If arTS Is Nothing OrElse arTS.Length < 10 Or arTS.Length > 11 Then Throw New IndexOutOfRangeException("10 or 11 values were not returned on line " & tfpTS.LineNumber)
                            Dim ParameterID As String = arTS(1)
                            Dim LocID As String = arTS(2)
                            Dim NumRanges As Integer = arTS(4)
                            Dim NumPts As Integer = arTS(5) 'total points returned?
                            If NumPts > 0 Then 'may say one range but no points
                                Dim NumPoints(NumRanges - 1) As Integer
                                For i As Integer = 0 To NumRanges - 1
                                    tfpTS.ReadLine() 'skip header line before table 1
                                    arTS = tfpTS.ReadFields
                                    If arTS Is Nothing OrElse arTS.Length <> 4 Then Throw New IndexOutOfRangeException("4 values were not returned on line " & tfpTS.LineNumber)
                                    NumPoints(i) = arTS(3)
                                Next
                                For i As Integer = 0 To NumRanges - 1
                                    Dim line As String = tfpTS.ReadLine()
                                    For j As Integer = 0 To NumPoints(i) - 1
                                        arTS = tfpTS.ReadFields
                                        If arTS Is Nothing OrElse arTS.Length <> 6 Then Throw New IndexOutOfRangeException("6 values were not returned on line " & tfpTS.LineNumber)
                                        Dim time As DateTime = CDate(arTS(1))
                                        Dim result As Double = Val(arTS(2))
                                        Dim grade As String = TestNull(arTS(3), "") 'this is two digit code (31=good)
                                        Dim approval As String = arTS(5) 'this is 1 digit approval code (1=raw,2=in progress,3=approved)

                                        If Val(grade) > -2 Then 'assume -2 and less means unusable; don't import
                                            recid += 1

                                            Dim sql As String = $"INSERT INTO {Project.FullTableName(WRDBTable)} 
                                                            (RecID, Station_ID, PCode, Date_Time, Result, Depth_M, SCode, QCode, LCode, Create_Update, Owner) 
                                                            VALUES (
                                                                {recid}, 
                                                                {Project.DB.TextString(StationID.ToUpper, 25)}, 
                                                                {Project.DB.TextString(PCode.ToUpper, 10)}, 
                                                                {Project.DB.DateTime(time)}, 
                                                                {Project.DB.Numeric(result)},
                                                                {Project.DB.Numeric(Depth)}, 
                                                                {Project.DB.TextString(grade, 3)}, 
                                                                {Project.DB.TextString(approval, 1)}, 
                                                                {Project.DB.TextString(LCode.ToUpper, 3)},
                                                                {Project.DB.DateTime(Now)}, 
                                                                {Project.DB.TextString(clsApplicationSettings.UserName, 3)})"

                                            Project.DB.ExecuteNonQuery(tran, sql)
                                        End If
                                        NumRecords += 1
                                        Application.DoEvents()
                                        If ImportProgressForm.IsCancelled Then
                                            AppendLog($"Import cancelled by user while importing {kv.Key} to {WRDBTable}.")
                                            ErrorsOccurred = True
                                            tran.Rollback()
                                            Exit Sub
                                        End If
                                    Next
                                Next
                            End If
                            tfpTS.Close()
                            tfpTS.Dispose()
                        Next

                    Else 'use version 2

                        '5. get time series for specified dataset
                        'note that this API uses the DisplayID for the parameter whereas the V1 API used the ID
                        'note: API V2 does not support resampling of data

                        Dim url As String = $"{baseURL()}" &
                                $"{IIf(ImportRawData, "GetTimeSeriesRawData", "GetTimeSeriesCorrectedData")}" &
                                $"?TimeSeriesIdentifier={DatasetID}&GetParts=PointsOnly" &
                                $"&QueryFrom={FirstImportDate.ToString("yyyy-MM-ddTHH:mm:ss.fffzzz")}" &
                                $"&QueryTo={LastImportDate.ToString("yyyy-MM-ddTHH:mm:ss.fffzzz")}&Format=xml"
                        Dim ds As DataSet = DownloadXML(wc, url)

                        'there are two datatables of interest: TimeSeriesPoint which has TimeSeriesPoint_Id and Value; and Timestamp which has TimeSeriesPoint_Id and DateTime; they are joined using the TimeSeriesPoint_Timestamp relation
                        'want to join these two tables using the existing relation

                        If ds.Tables.Contains("TimeSeriesPoint") Then 'if no data in time range table not created
                            With ds.Tables("TimeSeriesPoint")
                                For Each rTS As DataRow In .Rows
                                    Dim Result As Double = TestNull(rTS("Value"), Double.NaN)
                                    Dim Time As Date = TestNull(rTS.GetChildRows("TimeSeriesPoint_Timestamp")(0)("DateTime"), DateTime.MinValue)
                                    Dim Grade As String = TestNull(rTS("Grade"), "")
                                    Dim Approval As String = TestNull(rTS("Approval"), "")
                                    If Not Double.IsNaN(Result) And Time <> DateTime.MinValue Then
                                        recid += 1
                                        Dim sql As String = $"INSERT INTO {Project.FullTableName(WRDBTable)} 
                                                            (RecID, Station_ID, PCode, Date_Time, Result, Depth_M, SCode, QCode, LCode, Create_Update, Owner) 
                                                            VALUES (
                                                                {recid}, 
                                                                {Project.DB.TextString(StationID.ToUpper, 25)}, 
                                                                {Project.DB.TextString(PCode.ToUpper, 10)}, 
                                                                {Project.DB.DateTime(Time)}, 
                                                                {Project.DB.Numeric(Result)}, 
                                                                {Project.DB.Numeric(Depth)}, 
                                                                {Project.DB.TextString(Grade.ToUpper, 3)}, 
                                                                {Project.DB.TextString(Approval.ToUpper, 1)}, 
                                                                {Project.DB.TextString(LCode.ToUpper, 3)}, 
                                                                {Project.DB.DateTime(Now)}, 
                                                                {Project.DB.TextString(clsApplicationSettings.UserName, 3)})"

                                        Project.DB.ExecuteNonQuery(tran, sql)
                                        NumRecords += 1
                                        Application.DoEvents()
                                        If ImportProgressForm.IsCancelled Then
                                            AppendLog($"Import cancelled by user while importing {kv.Key} to {WRDBTable}.")
                                            ErrorsOccurred = True
                                            tran.Rollback()
                                            Exit Sub
                                        End If
                                    End If
                                Next
                            End With
                        End If
                        ds.Dispose()

                    End If

                    'update the support tables
                    With dictLocations(LocationID)

                        Dim sql As String = $"IF EXISTS (SELECT * FROM {Project.FullTableName(clsProject.enumTableTypes.Stations)} Stations WHERE Station_ID={Project.DB.TextString(StationID, 25)}) 
                                                UPDATE {Project.FullTableName(clsProject.enumTableTypes.Stations)} SET 
                                                    Station_Name={Project.DB.TextString(.Name, 60)},
                                                    Station_Type={Project.DB.TextString(.TypeName, 60).ToUpper},
                                                    Other={Project.DB.TextString(.Description, 50)},
                                                    Latitude={Project.DB.Numeric(.Latitude)},
                                                    Longitude={Project.DB.Numeric(.Longitude)},
                                                    Elevation={Project.DB.Numeric(.Elevation)},
                                                    Create_Update={Project.DB.DateTime(Now)} 
                                                    WHERE Station_ID={Project.DB.TextString(StationID, 25)}
                                                ELSE 
                                                INSERT INTO {Project.FullTableName(clsProject.enumTableTypes.Stations)} (Station_ID, Station_Name, Station_Type, Other, Latitude, Longitude, Elevation, Create_Update) 
                                                VALUES (
                                                    {Project.DB.TextString(StationID, 25)},
                                                    {Project.DB.TextString(.Name, 60)},
                                                    {Project.DB.TextString(.TypeName, 60).ToUpper},
                                                    {Project.DB.TextString(.Description, 50)},
                                                    {Project.DB.Numeric(.Latitude)},
                                                    {Project.DB.Numeric(.Longitude)},
                                                    {Project.DB.Numeric(.Elevation)},
                                                    {Project.DB.DateTime(Now)})"

                        Project.DB.ExecuteNonQuery(tran, sql)

                    End With

                    With dictParameters(DisplayID)

                        Dim sql As String = $"IF EXISTS (SELECT * FROM {Project.FullTableName(clsProject.enumTableTypes.PCodes)} PCodes WHERE PCode={Project.DB.TextString(PCode, 10)}) 
                                                UPDATE {Project.FullTableName(clsProject.enumTableTypes.PCodes)} SET 
                                                    Anal_Name={Project.DB.TextString(.Name, 60)},
                                                    Units={Project.DB.TextString(.Units, 60)}
                                                    WHERE PCode={Project.DB.TextString(PCode, 10)}
                                                ELSE 
                                                INSERT INTO {Project.FullTableName(clsProject.enumTableTypes.PCodes)} (PCode,Anal_Name,Units) 
                                                VALUES (
                                                    {Project.DB.TextString(PCode, 10)},
                                                    {Project.DB.TextString(.Name, 60)},
                                                    {Project.DB.TextString(.Units, 60)})"

                        Project.DB.ExecuteNonQuery(tran, sql)

                    End With

                Next

                If DeleteBeforeDate <> Date.MinValue Then
                    Project.DB.ExecuteNonQuery(tran, $"DELETE FROM {Project.FullTableName(WRDBTable)} 
                                                           WHERE Date_Time<{Project.DB.DateTime(DeleteBeforeDate)}")
                End If

                Dim dictGrades As SortedDictionary(Of String, clsAquariusGrade) = Nothing
                If APIVer = enumAPIVer.V1 Then dictGrades = Grades(client, token) Else dictGrades = Grades(wc)

                For Each kv In dictGrades
                    Dim sql As String = $"IF NOT EXISTS(
                                            SELECT * 
                                            FROM {Project.FullTableName(clsProject.enumTableTypes.Validation)} Validation 
                                            WHERE 
                                                Field_Name='SCODE' AND Valid_Value={Project.DB.TextString(kv.Value.Code, 3)})
                                              INSERT INTO {Project.FullTableName(clsProject.enumTableTypes.Validation)} (Field_Name,Valid_Value,Description)
                                              VALUES (
                                                {Project.DB.TextString("SCODE")},
                                                {Project.DB.TextString(kv.Value.Code, 120)},
                                                {Project.DB.TextString(kv.Value.Description, 120)})"
                    Project.DB.ExecuteNonQuery(tran, sql)
                Next

                Dim dictApprovals As SortedDictionary(Of String, clsAquariusApproval) = Nothing
                If APIVer = enumAPIVer.V1 Then dictApprovals = Approvals(client, token) Else dictApprovals = Approvals(wc)

                For Each kv In dictApprovals
                    Dim sql As String = $"IF NOT EXISTS(SELECT * FROM {Project.FullTableName(clsProject.enumTableTypes.QCodes)} QCodes WHERE QCode={Project.DB.TextString(kv.Value.Code, 1)})
                                              INSERT INTO {Project.FullTableName(clsProject.enumTableTypes.QCodes)} (QCode,QCode_Name)
                                              VALUES (
                                                {Project.DB.TextString(kv.Value.Code, 1)},
                                                {Project.DB.TextString(kv.Value.Description, 30)})"
                    Project.DB.ExecuteNonQuery(tran, sql)
                Next

                tran.Commit()
                tran.Dispose()

                Project.UpdateTrackingRecord(WRDBTable, Project.GetRecordCount(WRDBTable), True)

                AppendLog($"Imported {NumRecords} new records to {WRDBProjectName}/{WRDBTable}.")

            Catch ex As Exception
                AppendLog("Error occurred while trying to import from Aquarius: {0}.", ex.ToString)
                Throw

            Finally
                If Project IsNot Nothing Then RemoveHandler Project.Progress, AddressOf Progress
            End Try

        Catch ex As Exception

            If tran IsNot Nothing Then tran.Rollback()
            ErrorsOccurred = True
            ErrorMsg(, ex)

        Finally

            If tran IsNot Nothing Then tran.Dispose()

        End Try

    End Sub

    Private Function DownloadXML(wc As WebClientEx, url As String) As DataSet

        'store in temp file
        Dim fn As String = IO.Path.Combine(IO.Path.GetTempPath, "WRDB.xml")

        'request data from service
        Dim uri As New Uri(url)
        wc.DownloadFile(uri, fn)

        Dim ds As New DataSet
        ds.ReadXml(fn)

        Return ds

    End Function

    ''' <summary>
    ''' Helper class so can set timeout on webclient and retain cookies
    ''' </summary>
    Private Class WebClientEx
        Inherits System.Net.WebClient

        ''' <summary>
        ''' Timeout in milliseconds
        ''' </summary>
        Public Property Timeout() As Integer

        Private cc As New Net.CookieContainer

        Protected Overrides Function GetWebRequest(uri As Uri) As Net.WebRequest
            Dim lWebRequest As Net.WebRequest = MyBase.GetWebRequest(uri)
            If lWebRequest IsNot Nothing Then
                lWebRequest.Timeout = Timeout
                With DirectCast(lWebRequest, Net.HttpWebRequest)
                    .ReadWriteTimeout = Timeout
                    .CookieContainer = cc
                End With
            End If
            Return lWebRequest
        End Function

    End Class

End Class

